﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Linq;
using System.Threading.Tasks;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using System.IO;
using System.Data.OleDb; 
using UFIDA.U8.DB;
using UFIDA.U8C.AnLianDB;
using Newtonsoft.Json.Linq; 
using System.Net;
using Newtonsoft.Json;
using UFIDA.U8.PUB;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using System.Data.SqlClient;

namespace AnLianSysClient
{
   

    public partial class BankDetailFrm : DevExpress.XtraEditors.XtraForm
    { 

        #region 初始化
        private string strExcelName = string.Empty;     //excel文件路径
        private string _accid = ""; 

        private static string u8curl = common.getConfigInfo("u8curl");
        private static string glurl = u8curl+ "/u8cloud/api/gl/voucher/insert";

        private string customerurl = u8curl + "/u8cloud/api/uapbd/custdoc/insert";

        const string regKey = "DevExpress\\XtraGrid\\Layouts\\BankDetail";
        private string _corp = "";
        private string _glorgbook = "";
        private DataTable dtaccconfig = null;
        public BankDetailFrm()
        {
            InitializeComponent();
        }


        private void BankDetailFrm_Load(object sender, EventArgs e)
        {
            gdDetail.MainView.RestoreLayoutFromRegistry(regKey);//读取（还原）布局 
            //加载公司列表
            this.addAccNo();
        }

        private void addAccNo()
        {
            string sql = "select * from cust_accconfig";
            dtaccconfig = DbHelperSQL.QueryAsDT(sql, common.bankconn());
            if (dtaccconfig != null && dtaccconfig.Rows.Count > 0)
            {
                foreach (DataRow dr in dtaccconfig.Rows)
                {
                    edtAccNo.Properties.Items.Add(dr["record"].ToString());
                }
            }
        }
 

        private void BankDetailFrm_FormClosed(object sender, FormClosedEventArgs e)
        {
            gdDetail.MainView.SaveLayoutToRegistry(regKey); //保存布局
        }
       

        private void GrvDetail_CustomDrawRowIndicator(object sender, DevExpress.XtraGrid.Views.Grid.RowIndicatorCustomDrawEventArgs e)
        {
            if (e.Info.IsRowIndicator && e.RowHandle >= 0)
            {
                e.Info.DisplayText = Convert.ToString(e.RowHandle + 1);
            }
        }
        #endregion

        #region 查询

        private void btnQuery_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            
            this.Cursor = Cursors.WaitCursor;
            try
            { 
                //清除计算中间数据
                this.QueryData(); 
            }
            catch(Exception ex){
                MessageBox.Show(ex.Message);
            }
            
            this.Cursor = Cursors.Default;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        private string getFilter()
        {
            StringBuilder str = new StringBuilder();
            if (!string.IsNullOrEmpty(edtBgndate.Text))
            {
                str.Append(" and convert(varchar(10), transtime,121) >= '" + edtBgndate.EditValue + "'");
            }

            if (!string.IsNullOrEmpty(edtEnddate.Text))
            {
                str.Append(" and convert(varchar(10), transtime,121) <= '" + edtEnddate.EditValue + "'");
            }

            if (!string.IsNullOrEmpty(edtAccNo.Text))
            {
                str.Append(" and bankAcc like '%" + edtAccNo.Text + "%'");
            }

            if (!string.IsNullOrEmpty(edtOPAccNo.Text))
            {
                str.Append(" and oppAccNo like '%" + edtOPAccNo.Text + "%'");
            }

            if (!string.IsNullOrEmpty(edtYT.Text))
            {
                str.Append(" and uses like '%" + edtYT.Text + "%'");
            }

            if (!string.IsNullOrEmpty(cbedtType.Text))
            {
                str.Append(" and cbz = '" + cbedtType.Text + "'");
            }
            if (!string.IsNullOrEmpty(cbedtGL.Text))
            {
                string strgl = cbedtGL.Text;
                if (strgl.Equals("否"))
                {
                    str.Append(" and glflag = 0 ");
                }
                else if (strgl.Equals("是"))
                {
                    str.Append(" and glflag = 1 ");
                }

            }

            //过滤掉收款中内部转移的部分
            //str.Append(" and not exists (select 1 from syncdb..cust_Accconfig where  cust_Accconfig.record = cust_queryaccdetail.oppaccno and cust_queryaccdetail.cbz = 0)");
            return str.ToString();
        }

        private void QueryData()
        {
            string sql = "select *, case glflag when 1 then '是' else '否' end cflag from cust_queryaccdetail WHERE 1=1 ";
            try
            {
                if (edtAccNo.Text.IsNullOrEmpty())
                {
                    MessageBox.Show("银行账号必须先选");
                    return;
                }
                string dbgndate = edtBgndate.Text;
                string denddate = edtEnddate.Text;
                if (string.IsNullOrEmpty(dbgndate) || string.IsNullOrEmpty(denddate))
                {
                    MessageBox.Show("请先选择开始结束日期");
                    return;
                }
                string strWhere = this.getFilter();
                if (!string.IsNullOrEmpty(strWhere))
                {
                    sql += strWhere;
                }
                sql += " order by transtime";
                DataTable dt = UFIDA.U8.DB.DbHelperSQL.QueryAsDT(sql, common.bankconn());
                gdDetail.DataSource = dt;
            }
            catch (Exception ex)
            {
                throw;
            }
        }
        #endregion

        #region 导出

        /// <summary>
        /// 输出到excel
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnExport_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            //           
            try
            {
                //设置文件类型 
                saveFileDialog1.Filter = "excel备份文件（*.xls）|*.xlsx";

                //设置默认文件类型显示顺序 
                saveFileDialog1.FilterIndex = 1;

                //保存对话框是否记忆上次打开的目录 
                saveFileDialog1.RestoreDirectory = true;
                saveFileDialog1.FileName = "银行流水明细表"+DateTime.Now.ToString("yyyyMMdd") + ".xlsx";
                //点了保存按钮进入 
                if (saveFileDialog1.ShowDialog() == DialogResult.OK)
                {
                    string localFilePath = saveFileDialog1.FileName.ToString(); //获得文件路径 
                    

                    grvDetail.ExportToXlsx(localFilePath);
                }
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.Message);
            }
            
        }
        #endregion
        
        #region 批量生成凭证
        private void BtnBatch_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            if (grvDetail.SelectedRowsCount == 0)
            {
                MessageBox.Show("请选择数据");
                return;
            }
            try
            {
                this.SendBatchGL(grvDetail.GetSelectedRows());
                this.QueryData();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void SendBatchGL(int[] rows)
        {
            int icount = 0;
            //组织凭证Entity
            List<UFIDA.U8C.AnLianDB.queryaccdetailsList> mlist = new List<UFIDA.U8C.AnLianDB.queryaccdetailsList>();
            foreach (var item in rows)
            {
                DataRow dr = grvDetail.GetDataRow(item);
                if (dr != null)
                {
                    if (dr["glflag"].ObjToInt32() == 1)
                    {
                        if (MessageBox.Show(dr["serilaId"].ObjToString() + "交易流水已生成凭证，请先删除总账凭证","提醒", MessageBoxButtons.YesNo) == DialogResult.No)
                        {
                            return;
                        } 
                    }
                    mlist.Add(UFIDA.U8C.AnLianDB.DAL.cust_queryaccdetail.DataRowToModel(dr));
                }
            }
            if (mlist.Count > 0)
            {
                foreach (var item in mlist)
                {
                    string js = "";
                    string errMsg = "";
                    string resResult = "";
                    bool bpost = false;
                    

                    //检查档案是否存在
                    if (item.oppAccName.IndexOf("公司") > 0)
                    {
                        if (!UFIDA.U8C.AnLianDB.DAL.U8CDAL.ExistsCustdoc(item.oppAccName, UFIDA.U8C.AnLianDB.common.u8cconn()))
                        {
                            //U8C 无客商档案
                            LogHelper.WriteLine("客户档案" + item.oppAccName + "数据\n");
                            js = this.getCustomerJs(item.oppAccName, common.mysqlconn());
                            if (js.Equals(""))
                            {
                                continue;
                            }
                            bpost = common.PostU8C(item.oppAccName, js, customerurl, ref resResult, ref errMsg);
                            if (!bpost)
                            {
                                MessageBox.Show(errMsg);
                                break;
                            }
                        }
                    } 

                    js = this.getGLjs(item.serialId, common.bankconn());
                    if (js == "")
                    {
                        this.UpdAccDetaillag(item.serialId, 1, "", "", "", "", "无需要生成凭证", common.bankconn());
                        continue;
                    }
                    bpost = common.PostU8C(item.serialId, js, glurl, ref resResult, ref errMsg);
                    if (bpost)
                    {
                        JArray res = JArray.Parse(resResult);
                        string year = res[0]["year"].ToString();
                        string month = res[0]["period"].ToString();
                        string no = res[0]["no"].ToString();
                        string pk = res[0]["pk_voucher"].ToString();
                        this.UpdAccDetaillag(item.serialId, 1, year, month, no, pk, "", common.bankconn());
                        icount++;
                    }
                    else
                    {
                        this.UpdAccDetaillag(item.serialId, 0, "", "", "", "", errMsg, common.bankconn());
                    } 
                }
                
                MessageBox.Show("生成凭证成功,共生成" + icount + "条凭证");

            }
        }


        /// <summary>
        /// 按serialid 取借贷方分录
        /// </summary>
        /// <param name="serialId"></param>
        /// <param name="conn"></param>
        /// <returns></returns>
        private string getGLjs(string serialId, string conn)
        {
            string subjcode = "";
            string json = "";
            string cbz = "";
            string custclass = "";
            string custcode = "";
            string oppAccName = "";
            string custass = "73";
            string sql = "select * from vw_queryaccdetails where serialid = '"+ serialId +"'";
            DataTable dt = DbHelperSQL.QueryAsDT(sql, conn);
            if (dt!= null && dt.Rows.Count > 0)
            {
                json = Newtonsoft.Json.JsonConvert.SerializeObject(dt);
                JArray items = JArray.Parse(json);
                foreach (JToken item in items)
                {
                    //收款付款
                    cbz = item["cbz"].ToString();
                    //供应商
                    oppAccName = item["oppAccName"].ToString();
                    //custcode = common.getcubasdoc(oppAccName, ref custclass, common.u8cconn());
                    //付款类凭证需要匹配对方账户
                    if (cbz.Equals("支出")) 
                    {
                        //查找u8c供应商档案
                        if (!string.IsNullOrEmpty(custcode))
                        {
                            if (custclass.Equals("劳务供应商"))   //劳务类供应商
                            {
                                item["pk_accsubj"] = "22020102";
                            }
                            else  //其他供应商
                            {
                                item["pk_accsubj"] = "22020203";
                            }
                        }
                        else  //没有找到客商档案
                        {
                            item["pk_accsubj"] = "660298";
                        }
                    }
                    else  //收入
                    {

                    }
                    //辅助核算
                    subjcode = item["pk_accsubj"].ToString();

                    //银行存款
                    if (subjcode.StartsWith("1002"))    
                    {
                        
                    }
                    else if (subjcode.Equals("660298"))
                    {
                        JArray cash = new JArray();
                        string dept = item["dept"].ToString();
                        JArray ass = new JArray();
                        ass.Add(new JObject()
                        {
                            { "checktypecode","2"},
                            { "checkvaluecode",dept}
                        });
                        ass.Add(new JObject()
                            {
                                { "checktypecode","J1Ass"},
                                { "checkvaluecode","10283"}
                            });

                        item["ass"] = ass;
                        Cash cashmodel = new Cash();
                        cashmodel.money = item["debitamount"].ToString().ObjToDecimal() == 0  ? item["creditamount"].ToString() : item["debitamount"].ToString();
                        cashmodel.moneymain = item["pk_currtype"].ToString();
                        cashmodel.pk_cashflow = item["cashflowno"].ToString();

                        cash.Add(JObject.FromObject(cashmodel));

                        item["cashflow"] = cash;
                    }
                    else 
                    {
                        //辅助核算1 客户档案 CU03335  
                        JArray ass = new JArray();
                        ass.Add(new JObject()
                        {
                            { "checktypecode",custass},
                            { "checkvaluecode", string.IsNullOrEmpty(item["fzcode"].ToString())  ?"CU0335":item["fzcode"].ToString()}
                        });
                        //if (!subjcode.Equals("22020203"))
                        //{
                        //    //辅助核销2 项目档案 默认值 10283
                        //    ass.Add(new JObject()
                        //    {
                        //        { "checktypecode","J1Ass"},
                        //        { "checkvaluecode","10283"}
                        //    });
                        //}
                       
                        JArray cash = new JArray();
                        Cash cashmodel = new Cash();
                        cashmodel.money = item["debitamount"].ToString().ObjToDecimal() == 0? item["creditamount"].ToString() : item["debitamount"].ToString();
                        cashmodel.moneymain = item["pk_currtype"].ToString();
                        cashmodel.pk_cashflow = item["cashflowno"].ToString();

                        cash.Add(JObject.FromObject(cashmodel));
                        item["ass"] = ass;
                        item["cashflow"] = cash;

                    } 
                }
                JObject req = new JObject();
                JArray head = new JArray();
                JObject voucher = new JObject();
                voucher["pk_corp"] = _corp;
                voucher["pk_glorgbook"] = _glorgbook;
                voucher["pk_prepared"] = common.getConfigInfo("u8cgluser"); // "18606510717";
                voucher["pk_vouchertype"] = "记账";
                voucher["prepareddate"] = edtEnddate.Text;
                voucher["details"] = items;
                head.Add(voucher);
                req["voucher"] = head;
                return req.ToString();
            }

            return "";
        }

        private bool getU8CCust(string oppAccName, ref string custclass, string conn)
        {
            string sql = "select b.docname from bd_cubasdoc a inner join bd_defdoc b on b.PK_DEFDOC = a.def2 where a.CUSTNAME = '' ";
            DataTable dt = DbHelperSQL.QueryAsDT(sql, conn);
            if (dt!= null && dt.Rows.Count > 0)
            {
                custclass = dt.Rows[0]["docname"].ToString();
                return true;
            }
            return false;
        }

        private void UpdAccDetaillag(string serialId, int iflag, string year, string month, string no, string pk, string errMsg, string conn)
        {
            string sql ="";
            if (string.IsNullOrEmpty(errMsg))
            {
                sql = "update cust_queryaccdetail set glflag = " + iflag + ", Iyear = '" + year + "', Imonth = '" + month + "', inoid ='"+ year + month + "-" + no +"', pk='"+ pk +"', createtime = getdate() where serialId = '" + serialId + "'";
            }
            else
            {
                sql = "update cust_queryaccdetail set errMsg = '" + errMsg + "'  where serialId = '" + serialId + "'";
            }
            

            int icount = UFIDA.U8.DB.DbHelperSQL.ExecuteSql(sql, conn);
        }

        #endregion

        #region 合并生成凭证
        private void BtnMerge_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            if (grvDetail.SelectedRowsCount == 0)
            {
                MessageBox.Show("请选择数据");
                return;
            }
            try
            {
                this.SendGLHB(grvDetail.GetSelectedRows());
                this.QueryData();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }


        /// <summary>
        /// 组织凭证数据并生成凭证
        /// </summary>
        /// <param name="rows"></param>

        /// <summary>
        /// 生成合并凭证
        /// </summary>
        /// <param name="rows"></param>
        private void SendGLHB(int[] rows)
        {
            List<string> serialList = new List<string>();
            List<string> oppbankacc = new List<string>();
            foreach (var i in rows)
            {
                string serialNo = grvDetail.GetRowCellValue(i, "serialId").ObjToString();
                int glflag = grvDetail.GetRowCellValue(i, "glflag").ObjToInt32();
                if (glflag == 1)
                {
                    MessageBox.Show(serialNo + "交易流水已生成凭证，请先删除总账凭证");
                    return;
                }
                serialList.Add(serialNo);

                string opaccno = grvDetail.GetRowCellValue(i, "oppAccNo").ObjToString();
                string oppname = grvDetail.GetRowCellValue(i, "oppAccName").ObjToString();
                if (oppbankacc.Count > 0 )
                {
                    if (!oppbankacc.Contains(opaccno))
	                {
                        MessageBox.Show(opaccno + "对方账户不同，不能合并制单");
                        return;
	                } 
                }
                else
                {
                    oppbankacc.Add(opaccno);
                    //检查档案是否存在
                    if (oppname.IndexOf("公司") > 0)
                    {
                        if (!UFIDA.U8C.AnLianDB.DAL.U8CDAL.ExistsCustdoc(oppname, UFIDA.U8C.AnLianDB.common.u8cconn()))
                        {
                            //U8C 无客商档案
                            LogHelper.WriteLine("客户档案" + oppname + "数据\n");
                            string js = this.getCustomerJs(oppname, common.mysqlconn());
                            if (js.Equals(""))
                            {
                                continue;
                            }
                            string resResult = "";
                            string errMsg = "";
                            bool bpost = common.PostU8C(oppname, js, customerurl, ref resResult, ref errMsg);
                            if (!bpost)
                            {
                                MessageBox.Show(errMsg);
                                break;
                            }
                        }
                    } 
                }
            }
            //
            if (serialList.Count > 0)
            {
                //传U8C
                string js = "";
                string errMsg = "";
                string resResult = "";
                string cno = "";
                js = this.getHBGL(serialList, common.bankconn());
                bool bpost = common.PostU8C(Guid.NewGuid().ToString(), js, glurl, ref resResult, ref errMsg);
                 
                if (bpost)
                {
                    JArray res = JArray.Parse(resResult);
                    string year = res[0]["year"].ToString();
                    string month = res[0]["period"].ToString();
                    string no = res[0]["no"].ToString();
                    string pk = res[0]["pk_voucher"].ToString();
                    cno = year + month + "-" + no;
                    this.UpdAccdetailFlag(serialList, 1,year, month, cno, pk, "", common.bankconn());
                }
                else
                {
                    this.UpdAccdetailFlag(serialList, 0, "","","","", errMsg, common.bankconn());
                }

                MessageBox.Show("生成凭证成功,凭证号" + cno); 
            }
        }

        private void UpdAccdetailFlag(List<string> serialList, int iflag,  string year, string month, string no, string pk, string errMsg, string conn)
        {
            string sql = "";
            if (string.IsNullOrEmpty(errMsg))
            {
                sql = "update cust_queryaccdetail set glflag = " + iflag + ", inoid = '" + no + "', iyear = '" + year + "', imonth = '" + month + "', pk = '" + pk + "', createtime = getdate() where serialId in ( '" + string.Join("','", serialList) + "')";
            }
            else
            {
                sql = "update cust_queryaccdetail set  errmsg = '"+ errMsg +"' where serialId in ( '" + string.Join("','", serialList) + "')";
            }
            
            int icount = UFIDA.U8.DB.DbHelperSQL.ExecuteSql(sql, conn);
        }

        private string getHBGL(List<string> serialList, string conn)
        {
            string json = "";
            
            string subjcode = "";
            #region 删除代码 改为存储过程取数
            /*
            string custclass = ""; 
            string custcode = "";
            string oppAccName = "";
            string custass = "73";
            bool bdebit = false;
            
            StringBuilder sql = new StringBuilder();
            sql.AppendLine(" select * from ( ");
            sql.AppendLine(" select case when sum(cast(isnull(inamt,0) as decimal(28,2))-cast(isnull(outamt,0) as decimal(28,2))) > 0 then sum(cast(isnull(inamt,0) as decimal(28,2))-cast(isnull(outamt,0) as decimal(28,2))) else 0 end as debitamount,  ");
            sql.AppendLine(" case when sum(cast(isnull(inamt,0) as decimal(28,2))-cast(isnull(outamt,0) as decimal(28,2))) > 0 then sum(cast(isnull(inamt,0) as decimal(28,2))- cast(isnull(outamt,0) as decimal(28,2))) else 0 end as localdebitamount,  ");
            sql.AppendLine(" case when sum(cast(isnull(inamt,0) as decimal(28,2))-cast(isnull(outamt,0) as decimal(28,2))) > 0 then 0 else -1* sum(cast(isnull(inamt,0) as decimal(28,2))-cast(isnull(outamt,0) as decimal(28,2))) end as creditamount,  ");
            sql.AppendLine(" case when sum(cast(isnull(inamt,0) as decimal(28,2))-cast(isnull(outamt,0) as decimal(28,2))) > 0 then 0 else -1* sum(cast(isnull(inamt,0) as decimal(28,2))-cast(isnull(outamt,0) as decimal(28,2))) end as localcreditamount, ");
            sql.AppendLine(" max(transtime) as transtime,  max(oppAccName) oppAccName, bankacc , '' as ass, '' as cashflow,   ");
            sql.AppendLine(" case when sum(cast(isnull(inamt,0) as decimal(28,2))-cast(isnull(outamt,0) as decimal(28,2))) > 0 then '1111' else '1121' end as cashflowno,  ");
            sql.AppendLine("  b.subjcode as pk_accsubj, 'CNY' as pk_currtype,   ");
            sql.AppendLine(" case when sum(cast(isnull(inamt,0) as decimal(28,2))-cast(isnull(outamt,0) as decimal(28,2))) > 0 then '收款' else  '付款' end   as explanation,  '23' as dept ");
            sql.AppendLine(" from cust_queryaccdetail a  ");
            sql.AppendLine(" left join u8cloud..bd_accsubj b on b.remcode = a.bankAcc and b.stoped ='N' AND b.pk_glorgbook = '0001A210000000000CCJ'");
            sql.AppendLine(" where a.serialid in ('" + string.Join("','", serialList) + "')");
            sql.AppendLine(" group by  a.bankacc,b.subjcode ");
            sql.AppendLine(" having  sum(cast(isnull(inamt,0) as decimal(28,2))-cast(isnull(outamt,0) as decimal(28,2))) != 0  ");

            sql.AppendLine(" union all ");

            sql.AppendLine(" select case when sum(cast(isnull(inamt,0) as decimal(28,2))-cast(isnull(outamt,0) as decimal(28,2))) > 0  then 0 else -1 * sum(cast(isnull(inamt,0) as decimal(28,2))-cast(isnull(outamt,0) as decimal(28,2)))  end as debitamount,  ");
            sql.AppendLine(" case when sum(cast(isnull(inamt,0) as decimal(28,2))-cast(isnull(outamt,0) as decimal(28,2))) > 0  then 0 else -1 * sum(cast(isnull(inamt,0) as decimal(28,2))-cast(isnull(outamt,0) as decimal(28,2)))  end as localdebitamount, ");
            sql.AppendLine(" case when sum(cast(isnull(inamt,0) as decimal(28,2))-cast(isnull(outamt,0) as decimal(28,2))) > 0  then sum(cast(isnull(inamt,0) as decimal(28,2))-cast(isnull(outamt,0) as decimal(28,2)))    else 0 end as creditamount,  ");
            sql.AppendLine(" case when sum(cast(isnull(inamt,0) as decimal(28,2))-cast(isnull(outamt,0) as decimal(28,2))) > 0  then sum(cast(isnull(inamt,0) as decimal(28,2))-cast(isnull(outamt,0) as decimal(28,2)))    else 0 end as localcreditamount, ");
            sql.AppendLine(" max(transtime) as transtime,   ");
            sql.AppendLine(" max(oppAccName) oppAccName,(a.oppAccNo) as bankacc, '' as ass, '' as cashflow ,  ");
            sql.AppendLine(" case when sum(cast(isnull(inamt,0) as decimal(28,2))-cast(isnull(outamt,0) as decimal(28,2))) > 0 then '1111' else '1121' end as cashflowno , ");
            sql.AppendLine(" case when sum(cast(isnull(inamt,0) as decimal(28,2))-cast(isnull(outamt,0) as decimal(28,2))) > 0  then  b.subjcode else '' end as pk_accsubj, 'CNY' as pk_currtype,   ");
            sql.AppendLine(" case when sum(cast(isnull(inamt,0) as decimal(28,2))-cast(isnull(outamt,0) as decimal(28,2))) > 0 then '收款' else  '付款' end   as explanation, '23' as dept ");
            sql.AppendLine(" from cust_queryaccdetail a ");
            sql.AppendLine(" left join u8cloud..bd_accsubj b on b.remcode = a.bankAcc and b.stoped ='N' AND b.pk_glorgbook = '0001A210000000000CCJ' ");
            sql.AppendLine(" where a.serialid in ('" + string.Join("','", serialList) + "')");
            sql.AppendLine(" group by a.oppAccno ,b.subjcode");
            sql.AppendLine(" having sum(cast(isnull(inamt,0) as decimal(28,2))-cast(isnull(outamt,0) as decimal(28,2))) != 0  ");
            sql.AppendLine(" ) a order by a.debitamount  desc ");
            DataTable dt = DbHelperSQL.QueryAsDT(sql.ToString(), conn);
             * */
            #endregion
            DataTable dt = this.getHBData(serialList, conn);
            if (dt != null && dt.Rows.Count > 0)
            {
                json = Newtonsoft.Json.JsonConvert.SerializeObject(dt);
                JArray items = JArray.Parse(json);
                foreach (JToken item in items)
                {
                    //供应商
                    //oppAccName = item["oppAccName"].ToString();
                    //custcode = common.getcubasdoc(oppAccName, ref custclass, common.u8cconn());
                    ////bcredit
                    //bdebit = item["debitamount"].ToString().ObjToDecimal() == 0  ?false:true;
                    
                    subjcode = item["pk_accsubj"].ToString();
                    #region 注释代码
                    //银行存款
                    //if (subjcode.StartsWith("1002"))
                    //{ 
                    //    //银行科目不做处理
                    //}
                    //else
                    //{
                    //    //应付利息
                    //    if (item["oppAccName"].ToString().Equals("应付利息"))
                    //    {
                    //        item["pk_accsubj"] = "";
                    //    }
                    //    else if (true) //ETC 预付账款
                    //    {
                            
                    //    }
                    //}
                    //    //JArray cash = new JArray();
                    //    //Cash cashmodel = new Cash();
                    //    //cashmodel.money = item["debitamount"].ToString().ObjToDecimal() == 0  item["creditamount"].ToString() : item["debitamount"].ToString();
                    //    //cashmodel.moneymain = item["pk_currtype"].ToString();
                    //    //cashmodel.pk_cashflow = item["cashflowno"].ToString();

                    //    //cash.Add(JObject.FromObject(cashmodel));
                    //    //银行帐号为辅助核算
                    //    JArray ass = new JArray();
                    //    ass.Add(new JObject()
                    //    {
                    //        { "checktypecode","96"},
                    //        { "checkvaluecode",item["bankacc"].ToString()}
                    //    });
                    //    item["ass"] = ass;
                    //    //item["cashflow"] = cash;
                    //} 
                    //else  
                    //{ 
                    //    //查找u8c供应商档案
                    //    if (!string.IsNullOrEmpty(custcode))
                    //    {
                    //        if (custclass.Equals("劳务供应商"))   //劳务类供应商
                    //        {
                    //            item["pk_accsubj"] = "22020102";
                    //        }
                    //        else  //其他供应商
                    //        {
                    //            item["pk_accsubj"] = "22020203";
                    //        }
                    //    }
                    //    else  //没有找到客商档案
                    //    {
                    //        item["pk_accsubj"] = "660298";
                    //    }
                    //    subjcode = item["pk_accsubj"].ToString();
                    //    custass = "73";
                    //    JArray ass = new JArray();
                    //    if (subjcode.Equals("660298"))
                    //    {
                    //        string dept = item["dept"].ToString();
                            
                    //        ass.Add(new JObject()
                    //        {
                    //            { "checktypecode","2"},
                    //            { "checkvaluecode",dept}
                    //        });
                    //        ass.Add(new JObject()
                    //        {
                    //            { "checktypecode","J1Ass"},
                    //            { "checkvaluecode","10283"}
                    //        });
                    //        item["ass"] = ass;
                    //    }
                    //    else
                    //    {
                    //        //辅助核算1 客户档案 CU03335 
                    //        ass = new JArray();
                    //        ass.Add(new JObject()
                    //        {
                    //            { "checktypecode",custass},
                    //            { "checkvaluecode", string.IsNullOrEmpty(custcode)  ?"CU0335":custcode}
                    //        });
                    //        if (subjcode.Equals("22020102"))
                    //        {
                    //            //辅助核销2 项目档案 默认值 10283
                    //            ass.Add(new JObject()
                    //            {
                    //                { "checktypecode","J1Ass"},
                    //                { "checkvaluecode","10283"}
                    //            });
                    //        }

                    //    }
                    #endregion
                    if (subjcode.Equals("")) //处理现金流量
	                {
                        JArray cash = new JArray();
                        Cash cashmodel = new Cash();
                        cashmodel.money = item["debitamount"].ToString().ObjToDecimal() == 0 ? item["creditamount"].ToString() : item["debitamount"].ToString();
                        cashmodel.moneymain = item["pk_currtype"].ToString();
                        cashmodel.pk_cashflow = item["cashflowno"].ToString();
                        cash.Add(JObject.FromObject(cashmodel));
 
                        item["cashflow"] = cash;
	                } 
                }
                JObject req = new JObject();
                JArray head = new JArray();
                JObject voucher = new JObject();
                voucher["pk_corp"] = _corp;
                voucher["pk_glorgbook"] = _glorgbook;
                voucher["pk_prepared"] = common.getConfigInfo("u8cgluser");// "18606510717";
                voucher["pk_vouchertype"] = "记账";
                voucher["prepareddate"] = edtEnddate.Text;
                voucher["details"] = items;
                head.Add(voucher);
                req["voucher"] = head;

                return req.ToString();
            }

            return "";
        }


        //调用合并取数存储过程
        private DataTable getHBData(List<string> serialList, string conn)
        {
            using (SqlConnection sqlconn = new SqlConnection(conn)) 
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    sqlconn.Open();
                    cmd.Connection = sqlconn;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "sp_hb_accdetail";
                    SqlParameter para = new SqlParameter("@serialid", SqlDbType.VarChar);
                    para.Value =  string.Join(",", serialList);
                    cmd.Parameters.Add(para);
                    SqlDataAdapter sda = new SqlDataAdapter(cmd);
                    DataTable dt = new DataTable();
                    sda.Fill(dt);
                    return dt;
                }
            }
        }

        #endregion

        private void edtAccNo_SelectedIndexChanged(object sender, EventArgs e)
        {
            string accno = edtAccNo.Text;
            if (string.IsNullOrEmpty(accno))
            {
                return;
            }
            dtaccconfig.DefaultView.RowFilter = "record = '"+ accno +"'";
            _corp = dtaccconfig.DefaultView[0]["corp"].ObjToString();
            _glorgbook = dtaccconfig.DefaultView[0]["glorgbook"].ObjToString();
        }

        private void grvDetail_SelectionChanged(object sender, DevExpress.Data.SelectionChangedEventArgs e)
        {
            try
            {
                int[] rows = grvDetail.GetSelectedRows();
                decimal iamount_s = 0;
                decimal iamount_f = 0;
                foreach (var item in rows)
                {
                    DataRow dr = grvDetail.GetDataRow(item);
                    if (dr != null  )
                    { 
                        iamount_s += dr["inamt"].ObjToDecimal();
                         
                        iamount_f += dr["outamt"].ObjToDecimal(); 
                    }
                }
                edtamtsum_s.Text = iamount_s.ToString();
                edtamtsum_f.Text = iamount_f.ToString();
                edtcount.Text = rows.Length.ToString();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            } 
        }


        private void UpdGLHD(int[] rows)
        {
            Dictionary<string, DataRow> inoIdList = new Dictionary<string, DataRow>();
            //组织凭证Entity queryaccdetailsList
            List<UFIDA.U8C.AnLianDB.queryaccdetailsList> mlist = new List<UFIDA.U8C.AnLianDB.queryaccdetailsList>();
            foreach (var item in rows)
            {
                DataRow dr = grvDetail.GetDataRow(item);
                if (dr != null)
                {
                    int glflag = dr["glflag"].ObjToInt32();
                    string inoid = dr["inoid"].ObjToString();
                    int iyear = dr["iyear"].ObjToInt32();
                    int imonth = dr["imonth"].ObjToInt32();
                    string serialid = dr["serialid"].ObjToString();
                    if (glflag != 1 || string.IsNullOrEmpty(inoid))
                    {
                        MessageBox.Show(this, dr["serialId"].ObjToString() + "交易流水未生成凭证，请先生成凭证");
                        return;
                    }

                    //回单附件处理
                    if (!inoIdList.ContainsKey(serialid))
                    {
                        inoIdList.Add(serialid, dr);
                    }
                }
            }

            if (inoIdList.Count > 0)
            {
                string sUpdinfo = "";
                bool bupload = this.UpLoadGLFile(_corp, inoIdList, ref sUpdinfo);
                MessageBox.Show("凭证列表" + sUpdinfo + " 上传回单完成");
            }
        }

        /// <summary>
        /// 上传文件
        /// </summary>
        /// <param name="accid"></param>
        /// <param name="LoginObj"></param>
        /// <param name="inoIdList"></param>
        /// <returns></returns>
        private bool UpLoadGLFile(string accid, Dictionary<string, DataRow> inoIdList, ref string sUpdinfo)
        {
            FileUpdLoadModel FileList = new UFIDA.U8C.AnLianDB.FileUpdLoadModel();
            string url = common.getConfigInfo("u8curl") + "/u8cloud/api/file/upload/base64";
            string errMsg = "";
            string resResult = "";
            foreach (var item in inoIdList)
            {
                List<FileItem> files = new List<UFIDA.U8C.AnLianDB.FileItem>();
                DataRow dr = item.Value;
                int inoid = dr["inoid"].ObjToInt32();
                int iyear = dr["iyear"].ObjToInt32();
                int imonth = dr["imonth"].ObjToInt32();
                string pk = dr["pk"].ObjToString();
                string serialId = dr["serialId"].ObjToString();

                UFIDA.U8C.AnLianDB.Model.cust_queryReceipt rep = UFIDA.U8C.AnLianDB.DAL.cust_queryReceipt.GetModel(item.Key, UFIDA.U8C.AnLianDB.common.bankconn());
                if (rep == null || string.IsNullOrEmpty(rep.downloadUrl) || rep.downloadUrl.Equals("无回单信息"))
                {
                    //MessageBox.Show("交易流水" + item.Key + "凭证" + item.Value + "没有获取到回单下载信息");
                    //sUpdinfo += "交易流水" + item.Key + "凭证" + item.Value + "没有回单下载信息\n";
                    continue;
                }
                string fileid = System.IO.Path.GetFileName(rep.downloadUrl);
                
                if (!rep.fileid.IsNullOrEmpty())
                {
                    fileid = rep.fileid;
                }
                if (!string.IsNullOrEmpty(fileid))
                {
                    //判断文件id是否已存在
                    //if (this.ExistsFile(rep.fileid, inoid, iyear, imonth))
                    //{
                    //    continue;
                    //}
                   
                    bool bdown = DownLoadFile(sPath , fileid, rep.downloadUrl);
                    if (bdown)
                    { 
                        string filename = sPath + @"\" + fileid + ".zip";
                        //bool bupload = this.AddU8File(filename, ref fileid);
                        //if (bupload && !string.IsNullOrEmpty(fileid))
                        //{
                        
                        //    //AddAccAttach(inoid, iyear, imonth, fileid, filename, 1);
                        //    sUpdinfo += inoid + ";";
                        //}
                        FileItem file = new UFIDA.U8C.AnLianDB.FileItem();
                        file.billid = pk;
                        file.billtype = "voucher";
                        file.creator = common.getConfigInfo("u8cgluser");
                        file.file_name = fileid + ".zip";
                        file.upload_path = pk + "/api/upload";
                        file.content = FileToBase64String(filename);
                        files.Add(file);

                        FileList.file = files;
                        string js = JsonConvert.SerializeObject(FileList);

                        string time = DateTime.Now.ToString("yyyyMMddHHmmssfff");
                        bool bpost = common.PostU8C(time, js, url, ref resResult, ref errMsg);
                        if (!bpost)
                        {
                            sUpdinfo = "交易流水号" + serialId + "上传附件失败：" + errMsg +"\n";
                            continue;
                        }
                    }
                }

            } 

            return true;
        }

        public string FileToBase64String(string path)
        {
            // 指定文件路径
            FileStream fsForRead = new FileStream(path, FileMode.Open);
            string base64Str = "";
            try
            {
                // 将读写指针移到文件开头
                fsForRead.Seek(0, SeekOrigin.Begin);
                byte[] bs = new byte[fsForRead.Length];
                int log = Convert.ToInt32(fsForRead.Length);
                // 从文件中读取数据放到数组bs中
                fsForRead.Read(bs, 0, log);
                // 将字节数组转换为Base64字符串
                base64Str = Convert.ToBase64String(bs);
                return base64Str;
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
                Console.ReadLine();
                return base64Str;
            }
            finally
            {
                // 关闭文件流
                fsForRead.Close();
            }
        }
        /// <summary>
        /// 下载文件到本地
        /// </summary>
        /// <param name="localpath"></param>
        /// <param name="filename"></param>
        /// <param name="url"></param>
        /// <returns></returns>
        public static bool DownLoadFile(string localpath, string filename, string url)
        {
            try
            {
                WebClient wc = new WebClient();
                string cfile = localpath + @"\" + filename;
                if (File.Exists(cfile))
                {
                    File.Delete(cfile);
                }
                if (!Directory.Exists(localpath))
                {
                    Directory.CreateDirectory(localpath);
                }
                wc.DownloadFile(url, cfile + ".zip");
                return true;
            }
            catch (Exception ex)
            {
                UFIDA.U8.PUB.LogHelper.WriteLine("DownLoadFile Error:" + ex + "\n");
                throw ex;
            }
        }
        //文件下载默认路径
        private string sPath = @"d:\NBBankFile";


        //private bool ExistsFile(string fileid, int inoid, int iyear, int imonth)
        //{
        //    string sql = "select 1 from GL_AccAttachs with (nolock) where CClientFileName like '%" + fileid + "%' and ino_id = " + inoid + " and iyear = " + iyear + " and iperiod = " + imonth;
        //    return DbHelperSQL.Exists(sql, login.GetLoginInfo().ConnString4Dn);
        //}
        private void btnFJ_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            //
            if (grvDetail.SelectedRowsCount == 0)
            {
                MessageBox.Show("请选择数据");
                return;
            }
            try
            {
                this.UpdGLHD(grvDetail.GetSelectedRows());
            }
            catch (Exception ex)
            {
                //.WriteLog(ex.StackTrace);
                MessageBox.Show(ex.Message);
            }
        }

        private void BtnInput_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        { 
            //string dbgndate = edtBgndate.Text;
            //string denddate = edtEnddate.Text;
            //if (string.IsNullOrEmpty(dbgndate) || string.IsNullOrEmpty(denddate))
            //{
            //    MessageBox.Show("请先选择开始结束日期");
            //    return;
            //}

            //string sql = "delete from cust_queryaccdetail where ddate >= '" + dbgndate + "' and ddate <='" + denddate + "'";
            string strExcelName = "";
            try
            {
                openFileDialog1.FileName = "";
                openFileDialog1.Filter = "All files (*.*)|*.*|Excel files (*.xls)|*.xls|Excel files (*.xlsx)|*.xlsx";
                openFileDialog1.FilterIndex = 3;
                openFileDialog1.RestoreDirectory = true;
                //导入临时表
                if (openFileDialog1.ShowDialog() == DialogResult.OK)
                {
                    //读取excel                        
                    strExcelName = this.openFileDialog1.FileName;

                    System.Data.DataTable dtfx = new System.Data.DataTable("ExcelTable");
                    dtfx = this.GetExcelToDataTableBySheet(strExcelName, "", true);
                    if (dtfx != null && dtfx.Rows.Count > 0)
                    {
                        //DbHelperSQL.ExecuteSql(sql, common.bankconn());
                        DbHelperSQL.ImportDataByDataTable2(dtfx, "cust_queryaccdetail", UFIDA.U8C.AnLianDB.common.bankconn());

                        string sql = "update cust_queryaccdetail set inamt = case when inamt is null then 0 when inamt = '' then 0 else inamt end where inamt is null or inamt = ''";
                        DbHelperSQL.ExecuteSql(sql, UFIDA.U8C.AnLianDB.common.bankconn());
                        sql = "update cust_queryaccdetail set outamt = case when outamt is null then 0 when outamt = '' then 0 else outamt end where outamt is null or outamt = '';";
                        DbHelperSQL.ExecuteSql(sql, UFIDA.U8C.AnLianDB.common.bankconn());
                        sql = "update  cust_queryaccdetail set serialid = id where isnull(serialid, '') = ''";
                        DbHelperSQL.ExecuteSql(sql, UFIDA.U8C.AnLianDB.common.bankconn());
                            
                        MessageBox.Show("导入完成");
                        //this.QueryData();
                    }
                }
            }
            catch (Exception se)
            {
                LogHelper.WriteLine(se.StackTrace);
                MessageBox.Show(se.Message);
            }
        }

        /// <summary>
        /// excel转换datatable
        /// </summary>
        /// <param name="FileFullPath"></param>
        /// <returns></returns>
        private System.Data.DataTable GetExcelToDataTableBySheet(string FileFullPath, string sheetName, bool isFirstRowColumn)
        {
            ISheet sheet = null;
            System.Data.DataTable data = new System.Data.DataTable();
            int startRow = 0;
            HSSFWorkbook HSFbook = null;
            XSSFWorkbook XSFbook = null;
            ICell cell = null;
            try
            {
                FileStream fs = new FileStream(FileFullPath, FileMode.Open, FileAccess.Read);
                if (FileFullPath.IndexOf(".xlsx") > 0)
                { // 2007版本
                    XSFbook = new XSSFWorkbook(fs);
                    if (!string.IsNullOrEmpty(sheetName))
                    {
                        sheet = XSFbook.GetSheet(sheetName);
                    }
                    else
                    {
                        sheet = XSFbook.GetSheetAt(0);
                    }
                }

                else if (FileFullPath.IndexOf(".xls") > 0) // 2003版本
                {
                    HSFbook = new HSSFWorkbook(fs);
                    if (!string.IsNullOrEmpty(sheetName))
                    {
                        sheet = HSFbook.GetSheet(sheetName);
                    }
                    else
                    {
                        sheet = HSFbook.GetSheetAt(0);
                    }
                }


                if (sheet != null)
                {
                    int rowCount = sheet.LastRowNum;
                    if (rowCount <= 1)
                    {
                        throw new Exception("行数不对，请检查是否有标题行及字段名行");
                    }
                    IRow captionrow = sheet.GetRow(0);
                    IRow firstRow = sheet.GetRow(1);
                    int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
                    if (isFirstRowColumn)
                    {

                        for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                        {
                            DataColumn column = new DataColumn(firstRow.GetCell(i).StringCellValue);
                            data.Columns.Add(column);
                        }
                        startRow = firstRow.RowNum + 1;
                    }
                    else
                    {
                        startRow = sheet.FirstRowNum;
                    }
                    //最后一列的标号

                    for (int i = startRow; i <= rowCount; ++i)
                    {

                        IRow row = sheet.GetRow(i);
                        if (row == null) continue; //没有数据的行默认是null　　　　　　　

                        DataRow dataRow = data.NewRow();
                        for (int j = row.FirstCellNum; j < cellCount; ++j)
                        {
                            cell = row.GetCell(j);
                            if (cell != null)
                            {
                                switch (cell.CellType)
                                {
                                    case CellType.Blank:
                                        dataRow[j] = "";
                                        break;
                                    case CellType.Numeric:
                                        short format = cell.CellStyle.DataFormat;
                                        //对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理
                                        if (format == 14 || format == 31 || format == 57 || format == 58 || format == 177 || format == 181 || format == 179 || format == 178 || format == 176)
                                            dataRow[j] = cell.DateCellValue.ToString("yyyy-MM-dd");
                                        else
                                            dataRow[j] = cell.NumericCellValue;
                                        break;
                                    case CellType.String:
                                      
                                        dataRow[j] = cell.StringCellValue == "" ?  null : cell.StringCellValue;
                                        
                                        break;
                                }
                            }
                            else
                            {
                                dataRow[j] = "";
                            }
                            //if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
                            //    dataRow[j] = row.GetCell(j).ToString();
                        }
                        data.Rows.Add(dataRow);
                    }
                }
                return data;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                //Console.WriteLine("Exception: " + ex.Message);
                return null;
            }

        }
        #region 生成客商档案
        private void BtnBuildKS_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            if (grvDetail.SelectedRowsCount == 0)
            {
                MessageBox.Show("请选择数据");
                return;
            }
            try
            {
                this.BuildKS(grvDetail.GetSelectedRows()); 
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void BuildKS(int[] rows)
        {
            int icount = 0;
            string errMsg = "";
            string resResult = "";
            //组织凭证Entity
            List<UFIDA.U8C.AnLianDB.queryaccdetailsList> mlist = new List<UFIDA.U8C.AnLianDB.queryaccdetailsList>(); 
            foreach (var item in rows)
            {
                DataRow dr = grvDetail.GetDataRow(item);
                if (dr != null)
                {
                    string vname = dr["accname"].ToString();
                    if (vname.IndexOf("公司") > 0)
                    {
                        if (!UFIDA.U8C.AnLianDB.DAL.U8CDAL.ExistsCustdoc(vname, UFIDA.U8C.AnLianDB.common.u8cconn()))
                        {
                            //U8C 无客商档案
                            LogHelper.WriteLine("客户档案" + vname + "数据\n");
                            string js = this.getCustomerJs(vname, common.mysqlconn());
                            if (js.Equals(""))
                            {
                                continue;
                            }
                            bool bpost = common.PostU8C(vname, js, customerurl, ref resResult, ref errMsg);
                            if (bpost)
                            {
                                icount++;
                            }
                        }  
                    } 
                }
            }
            MessageBox.Show("一共生成" + icount + "个客商档案");
        }

        private string getCustomerJs(string vname, string u8conn)
        {
            
            JObject cbdocvo = new JObject();
            JArray custList = new JArray();

            JObject parentvo = new JObject();
            JObject cust = new JObject();

            cust["custshortname"] = vname;
            cust["custname"] = vname; 
            cust["pk_areacl"] = "0001"; 

            parentvo["parentvo"] = cust;
            custList.Add(parentvo); 
            cbdocvo["cbdocvo"] = custList;

            return cbdocvo.ToString();
           
        }
        #endregion

        private void BtnHBGL_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            if (grvDetail.SelectedRowsCount == 0)
            {
                MessageBox.Show("请选择数据");
                return;
            }
            try
            {
                this.SendGLHB(grvDetail.GetSelectedRows());
                this.QueryData();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void btnDel_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            if (grvDetail.SelectedRowsCount == 0)
            {
                MessageBox.Show("请选择数据");
                return;
            }
            try
            {
                this.DelDetail(grvDetail.GetSelectedRows());
                this.QueryData();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void DelDetail(int[] rows)
        {
            List<string> serialList = new List<string>(); 
            foreach (var i in rows)
            {
                string serialNo = grvDetail.GetRowCellValue(i, "serialId").ObjToString();
                int glflag = grvDetail.GetRowCellValue(i, "glflag").ObjToInt32();
                if (glflag == 1)
                {
                    MessageBox.Show(serialNo + "交易流水已生成凭证，请先删除总账凭证");
                    return;
                }
                serialList.Add(serialNo); 
            }
            //
            if (serialList.Count > 0)
            {
                string sql = "delete cust_queryaccdetail a  where a.serialid in ('" + string.Join("','", serialList) +"')";
                DbHelperSQL.ExecuteSql(sql, UFIDA.U8C.AnLianDB.common.bankconn());
                MessageBox.Show("删除成功");
            }
        }
    }
}